import os
import numbers
import pandas as pd
import datetime as dt

from decimal import Decimal
from rqalpha.apis import *
from rqrisk import Risk
from rqalpha.const import DAYS_CNT
from rqalpha.mod import ModHandler
from rqalpha.model.trade import Trade



NEXTT_MYSQL_DB_HOST = os.getenv('NEXTT_MYSQL_DB_HOST', "192.168.12.110")

class DB_CONF:
    host = NEXTT_MYSQL_DB_HOST
    port = '3309'
    user = "admin"
    password = "admin"
    database = "nextt"


KSH_MIN_AMOUNT = 200

def _get_account_position_ins(id_or_ins):
    ins = assure_instrument(id_or_ins)
    try:
        account = Environment.get_instance().portfolio.accounts[DEFAULT_ACCOUNT_TYPE.STOCK]
    except KeyError:
        raise KeyError(_(
                u"order_book_id: {order_book_id} needs stock account, please set and try again!"
            ).format(order_book_id=ins.order_book_id))
    position = account.get_position(ins.order_book_id, POSITION_DIRECTION.LONG)
    return account, position, ins

def _get_ksh_amount(amount):
    return 0 if abs(amount) < KSH_MIN_AMOUNT else amount // 1


def _is_ksh(ins):
    return ins.type == "CS" and ins.board_type == "KSH"


def update_cfg(cfg, param):
    #
    for k,v in param.items():
        # 策列参数传递
        if k in cfg['base']:
            cfg['base'][k] = v
        else:
            if 'params' not in cfg:
                cfg['params'] = {}
            cfg['params'][k] = v
            #
            if k == 'benchmark':
                cfg['mod']['sys_analyser'][k] = v
    return cfg


class MysqlStockPool(object):
    host = NEXTT_MYSQL_DB_HOST
    port = '3309'
    user = "admin"
    password = "admin"
    database = "jq"

    def __init__(self, sql, start_date=None, end_date=None):
        self._mysql_conn = None
        self._sql = sql
        # self._factor_name = factor_name
        self._trades_open = {}
        self._trades_close = {}

        self._env = None
        self._result_dict = {}
        #
        self._codes = self._load_pool_by_sql(sql)

    def _load_pool_by_sql(self, sql):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=self.user, password=self.password, host=self.host, database=self.database, port=self.port)
        #
        _cursor = self._mysql_conn.cursor()
        _cursor.execute(sql)
        all = _cursor.fetchall()
        _cursor.close()
        # 转
        df = pd.DataFrame(all, columns=["date", "code", "factor"])
        for g,v in df.groupby(['date']):
            self._result_dict[g] = v.sort_values(by=['factor'], ascending=False)
        return df

    def get_candidate_by_date(self, date):
        # 执行sql
        if date in self._result_dict:
            return self._result_dict[date]
        return None


class IndexStockPool(object):
    host = NEXTT_MYSQL_DB_HOST
    port = '3309'
    user = "admin"
    password = "admin"
    database = "jq"

    def __init__(self, pool_id, start_date=None, end_date=None):
        self._mysql_conn = None
        self._pool_id = pool_id
        # self._factor_name = factor_name
        self._trades_open = {}
        self._trades_close = {}
        self._portfolio_daily_returns = []
        self._benchmark_daily_returns = []
        self._total_portfolios = []
        self._total_benchmark_portfolios = []

        self._env = None
        self._result_dict = {}
        #
        self._codes = self._load_pool_by_index(pool_id, start_date, end_date)


    def _load_pool_by_index(self, index_ids, start_date=None, end_date=None):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=self.user, password=self.password, host=self.host, database=self.database, port=self.port)
        # 
        if isinstance(index_ids, str):
            index_ids = {index_ids: 1.0}
        df = pd.DataFrame()
        for index_id, weight in index_ids.items():
            if start_date and end_date:
                sql = "SELECT date,code,weight FROM tb_index_weights WHERE index_id = '{}' \
                    and date >= '{}' and date <= '{}';".format(index_id, start_date, end_date)
            else:
                sql = "SELECT date,code,weight FROM tb_index_weights WHERE index_id = '{}';".format(index_id)
            _cursor = self._mysql_conn.cursor()
            _cursor.execute(sql)
            all = _cursor.fetchall()
            _cursor.close()
            # 转
            one = pd.DataFrame(
                [
                    [i[0], i[1], i[2]*weight]
                    for i in all
                ],
                columns=["date", "code", "factor"],
            )
            df = pd.concat([df, one])
        # 
        for g,v in df.groupby(['date']):
            self._result_dict[g] = v.sort_values(by=['factor'], ascending=False)
        return df

    def get_pool_by_date(self, date):
        # 执行sql
        if date in self._result_dict:
            return self._result_dict[date]
        return None

    def get_candidate_by_date(self, date, threshold=None):
        # 执行sql
        if date in self._result_dict:
            if threshold:
                ret = self._result_dict[date]
                ret = ret[ret['factor'] > threshold]
                return ret['code'].tolist()
            else:
                return self._result_dict[date]['code'].tolist()
        return None


class CandidateStockPool(object):
    host = NEXTT_MYSQL_DB_HOST
    port = '3309'
    user = "admin"
    password = "admin"
    database = "nextt"

    def __init__(self, pool_id, start_date=None, end_date=None):
        self._mysql_conn = None
        self._pool_id = pool_id
        # self._factor_name = factor_name
        self._trades_open = {}
        self._trades_close = {}
        self._portfolio_daily_returns = []
        self._benchmark_daily_returns = []
        self._total_portfolios = []
        self._total_benchmark_portfolios = []

        self._env = None
        self._result_dict = {}
        self._filter_dict = {}
        #
        if pool_id.endswith('.csv'):
            self._codes = self._load_pool_by_csv(pool_id, start_date, end_date)
        else:
            self._codes = self._load_pool_by_id(pool_id, start_date, end_date)


    def _load_pool_by_id(self, pool_id, start_date=None, end_date=None):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=self.user, password=self.password, host=self.host, database=self.database, port=self.port)
        if start_date and end_date:
            sql = "SELECT date,code,factor FROM tb_factor_stock_pool WHERE pool_id = '{}' \
                and date >= '{}' and date <= '{}';".format(pool_id, start_date, end_date)
        else:
            sql = "SELECT date,code,factor FROM tb_factor_stock_pool WHERE pool_id = '{}';".format(pool_id)
        _cursor = self._mysql_conn.cursor()
        _cursor.execute(sql)
        all = _cursor.fetchall()
        _cursor.close()
        # 转
        df = pd.DataFrame(
            [
                [i[0], i[1], i[2]]
                for i in all
            ],
            columns=["date", "code", "factor"],
        )
        for g,v in df.groupby(['date']):
            self._result_dict[g] = v.sort_values(by=['factor'], ascending=False)
        # 波动
        filter_list = []
        for g,v in df.groupby(['code']):
            v['max'] = v['factor'].rolling(60).mean() + v['factor'].rolling(60).std() * 3
            filter_list.append(v[v['factor'] > v['max']])
        filter_df = pd.concat(filter_list)
        for g,v in filter_df.groupby(['date']):
            self._filter_dict[g] = v.sort_values(by=['factor'], ascending=False)
        return df


    def _load_pool_by_csv(self, csv_file, start_date=None, end_date=None):
        # 执行sql
        csv = pd.read_csv(csv_file)
        print(csv)
        csv['date'] = pd.to_datetime(csv['date'])
        df = csv[["date", "code", "factor"]] 
        # 转
        for g,v in df.groupby(['date']):
            self._result_dict[g.date()] = v.sort_values(by=['factor'], ascending=False)
        return df

    def get_candidate_by_date(self, date, threshold=None, is_short=False):
        # 执行sql
        if date in self._result_dict:
            if threshold:
                ret = self._result_dict[date]
                if is_short:
                    ret = ret[ret['factor'] < threshold]
                    return ret['code'].tolist()
                else:
                    ret = ret[ret['factor'] > threshold]
                    return ret['code'].tolist()
                pass
            else:
                return self._result_dict[date]['code'].tolist()
        return None

    def get_filter_by_date(self, date):
        # 执行sql
        if date in self._filter_dict:
            return self._filter_dict[date]['code'].tolist()
        return None


class GroupStockTrades(object):

    def __init__(self, group_id=None, root_id=None):
        self._mysql_conn = None
        self._group_id = group_id
        self._trades_open = {}
        self._trades_close = {}
        self._portfolio_daily_returns = []
        self._benchmark_daily_returns = []
        self._total_portfolios = []
        self._total_benchmark_portfolios = []

        self._env = None
        #
        if root_id is not None:
            self._ids = self._load_ids_by_root_id(root_id)
        else:
            self._ids = self._load_ids_by_group_id(group_id)
        self._result_dict = {}
        for id in self._ids:
            result = {}
            result["summary"] = self._load_summary_by_id([id])
            self._result_dict[id] = result

    def _load_ids_by_group_id(self, group_id):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=DB_CONF.user, password=DB_CONF.password, host=DB_CONF.host, database=DB_CONF.database, port=DB_CONF.port)
        _cursor = self._mysql_conn.cursor()
        sql = "SELECT DISTINCT id FROM tb_summary WHERE group_id = '{}' order by start_date;".format(group_id)
        _cursor.execute(sql)
        all = _cursor.fetchall()
        _cursor.close()
        ids = [i[0] for i in all]
        return ids

    def _load_ids_by_root_id(self, root_id):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=DB_CONF.user, password=DB_CONF.password, host=DB_CONF.host, database=DB_CONF.database, port=DB_CONF.port)
        _cursor = self._mysql_conn.cursor()
        sql = "SELECT DISTINCT id FROM tb_summary WHERE root_id = '{}' order by start_date;".format(root_id)
        _cursor.execute(sql)
        all = _cursor.fetchall()
        _cursor.close()
        ids = [i[0] for i in all]
        return ids
        
    def load_trades_by_id(self, start_date, end_date):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=DB_CONF.user, password=DB_CONF.password, host=DB_CONF.host, database=DB_CONF.database, port=DB_CONF.port)
        _cursor = self._mysql_conn.cursor()
        ids = self._ids
        if start_date and end_date:
            sql = "SELECT trading_datetime,order_book_id,side,position_effect,last_quantity,last_price FROM tb_trades WHERE id in ('{}') \
                and trading_datetime >= '{}' and trading_datetime <= '{}';".format("','".join(ids), start_date, end_date)
        else:
            sql = "SELECT trading_datetime,order_book_id,side,position_effect,last_quantity,last_price FROM tb_trades WHERE id in ('{}');".format("','".join(ids))
        _cursor.execute(sql)
        result = _cursor.fetchall()
        _cursor.close()
        # 转
        df = pd.DataFrame(
            result,
            columns=["trading_datetime", "order_book_id", "side", "position_effect", "last_quantity", "last_price"]
        )
        df['trading_date'] = df['trading_datetime'].apply(lambda x:x.date())
        df = df.set_index('trading_datetime').sort_index()
        for g,a in df.groupby(['trading_date']):
            for s,b in a.groupby(['position_effect']):
                if s == 'OPEN':
                    self._trades_open[g] = b
                else:
                    self._trades_close[g] = b
        return df


    def get_open_trades_by_date(self, trading_date):
        # 执行
        if trading_date in self._trades_open:
            return self._trades_open[trading_date]
        return None

    def get_close_trades_by_date(self, trading_date):
        # 执行
        if trading_date in self._trades_close:
            return self._trades_close[trading_date]
        return None

    def _load_summary_by_id(self, ids):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=DB_CONF.user, password=DB_CONF.password, host=DB_CONF.host, database=DB_CONF.database, port=DB_CONF.port)
        _cursor = self._mysql_conn.cursor()
        sql = "SELECT strategy_file,start_date,end_date,alpha,beta,sharpe,information_ratio,sortino,volatility,unit_net_value,downside_risk,tracking_error,max_drawdown, \
            total_returns,annualized_returns,benchmark_total_returns,benchmark_annualized_returns FROM tb_summary WHERE id in ('{}');".format("','".join(ids))
        _cursor.execute(sql)
        result = _cursor.fetchall()
        _cursor.close()
        # 转
        df = pd.DataFrame(
            [
                [i[0], i[1], i[2], i[3], i[4], i[5], i[6], i[7], i[8], i[9], i[10], i[11], i[12], i[13], i[14], i[15], i[16]]
                for i in result
            ],
            columns=["strategy_file", "start_date", "end_date", "alpha", "beta", "sharpe", "information_ratio", "sortino", "volatility", "unit_net_value", \
                "downside_risk", "tracking_error", "max_drawdown", "total_returns", "annualized_returns", "benchmark_total_returns", "benchmark_annualized_returns"],
        )
        return df.iloc[0]

    def _load_portfolio_by_id(self, ids):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=DB_CONF.user, password=DB_CONF.password, host=DB_CONF.host, database=DB_CONF.database, port=DB_CONF.port)
        _cursor = self._mysql_conn.cursor()
        sql = "SELECT cash,total_value,market_value,unit_net_value,units,static_unit_net_value,date \
            FROM tb_portfolio WHERE id in ('{}');".format("','".join(ids))
        _cursor.execute(sql)
        result = _cursor.fetchall()
        _cursor.close()
        # 转
        df = pd.DataFrame(
            [
                [i[0], i[1], i[2], i[3], i[4], i[5], i[6]]
                for i in result
            ],
            columns=["cash", "total_value", "market_value", "unit_net_value", "units", "static_unit_net_value", "date"],
        )
        df.set_index(['date'], inplace=True)
        return df
    
    def get_benchmark_daily_returns(self):
        #
        daily_return_list = []
        weights = 0
        for benchmark in [(self._env.config.mod.sys_analyser.benchmark, 1.0)]:
            bar = self._env.data_proxy.get_bar(benchmark[0], self._env.calendar_dt, '1d')
            if bar.close != bar.close:
                print(benchmark[0], self._env.calendar_dt)
                raise
            else:
                daily_return_list.append((bar.close / bar.prev_close - 1.0, benchmark[1]))
            weights += benchmark[1]
        return sum([daily[0]*daily[1]/weights for daily in daily_return_list])    

    @staticmethod
    def _safe_convert(value, ndigits=4):
        if isinstance(value, numbers.Real):
            return round(float(value), ndigits)

        return value

    def _calc_return_by_portfolio(self):
        #
        portfolio_dict = {}
        for id in self._ids:
            portfolio_dict[id] = {"portfolio": self._load_portfolio_by_id([id])}
        #
        plots_list = []
        ex_cum_factor = 1.0
        last_unit_net_value = None
        for id,rs in portfolio_dict.items():
            portfolio = rs['portfolio']
            first = portfolio.iloc[0]
            if last_unit_net_value is not None:
                last_day = self._env.data_proxy.get_previous_trading_date(first.name.date())
                assert(last_date == last_day)
                ex_cum_factor *= last_unit_net_value
            else:
                first_date = first.name.date()
            for idx,row in portfolio.iterrows():
                self._env.update_time(row.name, row.name)
                daily_returns = row.unit_net_value / row.static_unit_net_value - 1
                self._portfolio_daily_returns.append(daily_returns)
                self._benchmark_daily_returns.append(self.get_benchmark_daily_returns())
                self._total_portfolios.append({
                    'date': row.name.date(),
                    'cash': self._safe_convert(row.cash*ex_cum_factor),
                    'total_value': self._safe_convert(row.total_value*ex_cum_factor),
                    'market_value': self._safe_convert(row.market_value*ex_cum_factor),
                    'unit_net_value': self._safe_convert(row.unit_net_value*ex_cum_factor, 6),
                    'units': row.units,
                    'static_unit_net_value': self._safe_convert(row.static_unit_net_value*ex_cum_factor),
                })
                self._total_benchmark_portfolios.append({
                    "date": date,
                    "unit_net_value": (np.array(self._benchmark_daily_returns) + 1).prod()
                })
                plots_list.append({"分段除权净值":row.unit_net_value})
            #
            last_unit_net_value = row.unit_net_value
            last_date = row.name.date()
        #
        return first_date, last_date, plots_list


    def _calc_return_by_trades(self):
        #
        ex_cum_factor_list = []
        ex_cum_factor = 1.0
        last_unit_net_value = None

        min_date = None
        max_date = None
        for id,rs in self._result_dict.items():
            summary = rs['summary']
            min_date = summary.start_date if min_date is None else min(summary.start_date,  min_date)
            max_date = summary.end_date if max_date is None else max(summary.end_date,  max_date)
        min_date = dt.datetime.strptime(min_date, "%Y-%m-%d").date()
        max_date = dt.datetime.strptime(max_date, "%Y-%m-%d").date()

        trading_calendar = self._env.data_proxy.get_trading_dates(min_date, max_date)
        portfolio = self._env.portfolio
        account = portfolio.accounts[DEFAULT_ACCOUNT_TYPE.STOCK]
        #
        for trade_dt in trading_calendar:
            # print(trade_dt)
            self._env.update_time(trade_dt, trade_dt)
            with ExecutionContext(EXECUTION_PHASE.BEFORE_TRADING):
                portfolio._pre_before_trading(None)
                account._on_before_trading(None)
            order_id = 0
            trading_date = trade_dt.date()
            with ExecutionContext(EXECUTION_PHASE.ON_BAR):
                # 先处理平仓
                close_trades = self.get_close_trades_by_date(trading_date)
                if close_trades is not None:
                    for id,ct in close_trades.iterrows():
                        pos_sell = account.get_position(ct.order_book_id, POSITION_DIRECTION.LONG)
                        amount = pos_sell.closable
                        if amount == 0:
                            continue
                        deal_price = ct.last_price
                        # 是否限制涨跌停不成交
                        price_board = self._env.price_board
                        if deal_price <= price_board.get_limit_down(ct.order_book_id):
                            continue
                        order_id += 1
                        trade = Trade.__from_create__(
                            order_id=order_id,
                            price=deal_price,
                            amount=amount,
                            side=ct.side,
                            position_effect=ct.position_effect,
                            order_book_id=ct.order_book_id,
                            frozen_price=None,
                            close_today_amount=None
                        )
                        trade._commission = self._env.get_trade_commission(trade)
                        trade._tax = self._env.get_trade_tax(trade)
                        account.apply_trade(trade)
                        
                        # print(ct.order_book_id, amount, trade._commission, trade._tax)
                # 后处理开仓
                open_trades = self.get_open_trades_by_date(trading_date)
                if open_trades is not None:
                    for id,ot in open_trades.iterrows():
                        #
                        price = ot.last_price
                        account, position, ins = _get_account_position_ins(ot.order_book_id)
                        assert(position.quantity == 0)
                        
                        cash_amount = min(account.total_value * 0.01, account.cash)
                        amount = int(Decimal(cash_amount) / Decimal(price))
                        min_round_lot = int(ins.round_lot)
                        if _is_ksh(ins):
                            amount = _get_ksh_amount(amount)
                            min_round_lot = 1
                        else:
                            amount = int(Decimal(amount) / Decimal(min_round_lot)) * min_round_lot
                        while amount > 0:
                            expected_transaction_cost = self._env.get_order_transaction_cost(Order.__from_create__(
                                ins.order_book_id, amount, SIDE.BUY, LimitOrder(price), POSITION_EFFECT.OPEN
                            ))
                            if amount * price + expected_transaction_cost <= cash_amount:
                                break
                            amount -= min_round_lot
                        else:
                            continue
                        #
                        deal_price = price
                        # 是否限制涨跌停不成交
                        price_board = self._env.price_board
                        if deal_price >= price_board.get_limit_up(ot.order_book_id):
                            continue
                        #
                        order_id += 1
                        trade = Trade.__from_create__(
                            order_id=order_id,
                            price=deal_price,
                            amount=amount,
                            side=ot.side,
                            position_effect=ot.position_effect,
                            order_book_id=ot.order_book_id,
                            frozen_price=None,
                            close_today_amount=None
                        )
                        trade._commission = self._env.get_trade_commission(trade)
                        trade._tax = self._env.get_trade_tax(trade)
                        
                        account.apply_trade(trade)

                        # print(ot.order_book_id, ot.last_price, amount, trade._commission, trade._tax)
                #
                account._update_last_price(None)
            #
            with ExecutionContext(EXECUTION_PHASE.AFTER_TRADING):
                account._on_settlement(None)

            # positions = account.get_positions()
            # for p in positions:
            #     print(p.order_book_id, p.quantity, p.last_price, p.avg_price, p.market_value)
            #
            # print(portfolio.cash, portfolio.total_value, portfolio.market_value, portfolio.unit_net_value, portfolio.static_unit_net_value)
            daily_returns = portfolio.unit_net_value / portfolio.static_unit_net_value - 1
            self._portfolio_daily_returns.append(daily_returns)
            self._benchmark_daily_returns.append(self.get_benchmark_daily_returns())
            self._total_portfolios.append({
                'date': trading_date,
                'cash': self._safe_convert(portfolio.cash),
                'total_value': self._safe_convert(portfolio.total_value),
                'market_value': self._safe_convert(portfolio.market_value),
                'unit_net_value': self._safe_convert(portfolio.unit_net_value, 6),
                'units': portfolio.units,
                'static_unit_net_value': self._safe_convert(portfolio.static_unit_net_value),
            })
            self._total_benchmark_portfolios.append({
                "date": trading_date,
                "unit_net_value": (np.array(self._benchmark_daily_returns) + 1).prod()
            })
        #
        return min_date, max_date, None


    def concat_result_by_group(self):
        result_dict = {}
        #
        from rqalpha.utils.config import parse_config
        config = parse_config(__config__)
        self._env = Environment(config)
        
        mod_handler = ModHandler()

        mod_handler.set_env(self._env)
        mod_handler.start_up()

        default_bundle_path = os.path.abspath(os.path.expanduser('~/.rqalpha/bundle'))
        from rqalpha_mod_stu_min.data_source import St_Day_KDataSource
        self._env.set_data_source(St_Day_KDataSource(default_bundle_path))
        from rqalpha.data.bar_dict_price_board import BarDictPriceBoard
        self._env.price_board = BarDictPriceBoard()
        from rqalpha.data.data_proxy import DataProxy
        self._env.set_data_proxy(DataProxy(self._env.data_source, self._env.price_board))

        self._env.config.base.trading_calendar = self._env.data_proxy.get_trading_dates(self._env.config.base.start_date, self._env.config.base.end_date)
        self._env.config.base.start_date = self._env.config.base.trading_calendar[0].date()
        self._env.config.base.end_date = self._env.config.base.trading_calendar[-1].date()
        # FIXME
        start_dt = datetime.datetime.combine(self._env.config.base.start_date, datetime.datetime.min.time())
        self._env.update_time(start_dt, start_dt)

        from rqalpha.portfolio import Portfolio
        self._env.set_portfolio(Portfolio(
            self._env.config.base.accounts, self._env.config.base.init_positions, self._env.config.base.start_date, self._env.data_proxy, self._env.event_bus
        ))

        first_date, last_date, plots_list = self._calc_return_by_portfolio()

        strategy_name = os.path.basename(self._env.config.base.strategy_file).split(".")[0]
        summary = {
            'strategy_name': strategy_name,
            'start_date': first_date if isinstance(first_date, str) else first_date.strftime('%Y-%m-%d'),
            'end_date': last_date if isinstance(last_date, str) else last_date.strftime('%Y-%m-%d'),
            'strategy_file': self._env.config.base.strategy_file,
            'run_type': self._env.config.base.run_type.value,
        }
        for account_type, starting_cash in self._env.config.base.accounts.items():
            summary[account_type] = starting_cash

        risk_free_rate = self._env.data_proxy.get_risk_free_rate(first_date, last_date)
        risk = Risk(
            np.array(self._portfolio_daily_returns), np.array(self._benchmark_daily_returns), risk_free_rate
        )
        summary.update({
            'alpha': self._safe_convert(risk.alpha, 3),
            'beta': self._safe_convert(risk.beta, 3),
            'sharpe': self._safe_convert(risk.sharpe, 3),
            'excess_sharpe': self._safe_convert(risk.excess_sharpe, 3),
            'information_ratio': self._safe_convert(risk.information_ratio, 3),
            'downside_risk': self._safe_convert(risk.annual_downside_risk, 3),
            'tracking_error': self._safe_convert(risk.annual_tracking_error, 3),
            'sortino': self._safe_convert(risk.sortino, 3),
            'volatility': self._safe_convert(risk.annual_volatility, 3),
            'excess_volatility': self._safe_convert(risk.excess_volatility, 3),
            'excess_annual_volatility': self._safe_convert(risk.excess_annual_volatility, 3),
            'max_drawdown': self._safe_convert(risk.max_drawdown, 3),
            'excess_max_drawdown': self._safe_convert(risk.excess_max_drawdown),
            'excess_returns': self._safe_convert(risk.excess_return_rate, 6),
            'excess_annual_returns': self._safe_convert(risk.excess_annual_return, 6)
        })

        portfolio = self._total_portfolios[-1]

        date_count = float(self._env.data_proxy.count_trading_dates(first_date, last_date))
        annualized_returns = portfolio['unit_net_value'] ** (DAYS_CNT.TRADING_DAYS_A_YEAR / date_count) - 1

        portfolio['total_returns'] = portfolio['unit_net_value'] - 1
        portfolio['annualized_returns'] = annualized_returns
        summary.update({
            'total_value': self._safe_convert(portfolio['total_value']),
            'cash': self._safe_convert(portfolio['cash']),
            'total_returns': self._safe_convert(portfolio['total_returns'], 6),
            'annualized_returns': self._safe_convert(portfolio['annualized_returns']),
            'unit_net_value': self._safe_convert(portfolio['unit_net_value']),
            'units': portfolio['units'],
        })
        
        benchmark_total_returns = (np.array(self._benchmark_daily_returns) + 1.0).prod() - 1.0
        summary['benchmark_total_returns'] = self._safe_convert(benchmark_total_returns, ndigits=6)
        date_count = len(self._benchmark_daily_returns)
        benchmark_annualized_returns = (benchmark_total_returns + 1) ** (DAYS_CNT.TRADING_DAYS_A_YEAR / date_count) - 1
        summary['benchmark_annualized_returns'] = self._safe_convert(benchmark_annualized_returns, ndigits=6)

        result_dict["summary"] = summary

        df = pd.DataFrame(self._total_portfolios)
        df['date'] = pd.to_datetime(df['date'])
        total_portfolios = df.set_index('date').sort_index()
        #
        result_dict["portfolio"] = total_portfolios

        if plots_list is not None:
            df = pd.DataFrame(plots_list)
            result_dict["plots"] = df
        return result_dict



class PortfolioPlot(object):

    def __init__(self, id=None):
        self._mysql_conn = None
        self._id = id
        self._trades_open = {}
        self._trades_close = {}
        self._portfolio_daily_returns = []
        self._benchmark_daily_returns = []
        self._total_portfolios = []
        self._total_benchmark_portfolios = []

        self._env = None
        self._result_dict = {}
        #
        self._result_dict["summary"] = self._load_summary_by_id([id])
        self._result_dict["portfolio"] = self._load_portfolio_by_id([id])
        self._result_dict["benchmark_portfolio"] = self._load_benchmark_portfolio_by_id([id])


    def _load_summary_by_id(self, ids):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=DB_CONF.user, password=DB_CONF.password, host=DB_CONF.host, database=DB_CONF.database, port=DB_CONF.port)
        _cursor = self._mysql_conn.cursor()
        sql = "SELECT strategy_file,start_date,end_date,alpha,beta,sharpe,information_ratio,sortino,volatility,unit_net_value,downside_risk,tracking_error,max_drawdown, \
            total_returns,annualized_returns,benchmark_total_returns,benchmark_annualized_returns FROM tb_summary WHERE id in ('{}');".format("','".join(ids))
        _cursor.execute(sql)
        result = _cursor.fetchall()
        _cursor.close()
        # 转
        df = pd.DataFrame(
            [
                [i[0], i[1], i[2], i[3], i[4], i[5], i[6], i[7], i[8], i[9], i[10], i[11], i[12], i[13], i[14], i[15], i[16]]
                for i in result
            ],
            columns=["strategy_file", "start_date", "end_date", "alpha", "beta", "sharpe", "information_ratio", "sortino", "volatility", "unit_net_value", \
                "downside_risk", "tracking_error", "max_drawdown", "total_returns", "annualized_returns", "benchmark_total_returns", "benchmark_annualized_returns"],
        )
        return df.iloc[0]

    def _load_portfolio_by_id(self, ids):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=DB_CONF.user, password=DB_CONF.password, host=DB_CONF.host, database=DB_CONF.database, port=DB_CONF.port)
        _cursor = self._mysql_conn.cursor()
        sql = "SELECT cash,total_value,market_value,unit_net_value,units,static_unit_net_value,date \
            FROM tb_portfolio WHERE id in ('{}');".format("','".join(ids))
        _cursor.execute(sql)
        result = _cursor.fetchall()
        _cursor.close()
        # 转
        df = pd.DataFrame(
            [
                [i[0], i[1], i[2], i[3], i[4], i[5], i[6]]
                for i in result
            ],
            columns=["cash", "total_value", "market_value", "unit_net_value", "units", "static_unit_net_value", "date"],
        )
        df.set_index(['date'], inplace=True)
        return df
    
    def _load_benchmark_portfolio_by_id(self, ids):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=DB_CONF.user, password=DB_CONF.password, host=DB_CONF.host, database=DB_CONF.database, port=DB_CONF.port)
        _cursor = self._mysql_conn.cursor()
        sql = "SELECT unit_net_value,date \
            FROM tb_benchmark_portfolio WHERE id in ('{}');".format("','".join(ids))
        _cursor.execute(sql)
        all = _cursor.fetchall()
        _cursor.close()
        # 转
        df = pd.DataFrame(all,
            columns=["unit_net_value", "date"],
        )
        df.set_index(['date'], inplace=True)
        return df

    
    def get_results(self):
        #
        return self._result_dict



class GroupFutureTrades(object):

    def __init__(self, id):
        self._mysql_conn = None
        self._universe = {}
        self._trades_open = {}
        self._trades_close = {}
        #
        self._load_trades_by_root_id(id)

        
    def _load_trades_by_root_id(self, root_id):
        # 执行sql
        if self._mysql_conn is None:
            from mysql.connector import connect
            self._mysql_conn = connect(user=DB_CONF.user, password=DB_CONF.password, host=DB_CONF.host, database=DB_CONF.database, port=DB_CONF.port)
        _cursor = self._mysql_conn.cursor()
        sql = "SELECT id FROM tb_summary WHERE root_id = '{}';".format(root_id)
        _cursor.execute(sql)
        all = _cursor.fetchall()
        ids = set([i[0] for i in all])
        sql = "SELECT trading_datetime,order_book_id,side,position_effect,last_quantity,last_price FROM tb_trades WHERE id in ('{}');".format("','".join(ids))
        _cursor.execute(sql)
        result = _cursor.fetchall()
        _cursor.close()
        # 转
        df = pd.DataFrame(
            [
                [i[0], i[1], i[2], i[3], i[4], i[5]]
                for i in result
            ],
            columns=["trading_datetime", "order_book_id", "side", "position_effect", "last_quantity", "last_price"],
        )
        df['trading_date'] = df['trading_datetime'].apply(lambda x:x.date())
        df.set_index('trading_datetime', inplace=True)
        for g,a in df.groupby(['trading_date']):
            self._universe[g] = set(a['order_book_id'].tolist())
            for s,b in a.groupby(['position_effect']):
                if s == 'OPEN':
                    self._trades_open[g] = b
                else:
                    self._trades_close[g] = b
        pass

    def get_universe_by_date(self, trading_date):
        # 执行
        if trading_date in self._universe:
            return self._universe[trading_date]
        return None
        
    def get_open_trades_by_date(self, trading_date):
        # 执行
        if trading_date in self._trades_open:
            trades_open = {}
            tmp = self._trades_open[trading_date]
            for g,t in tmp.groupby(['trading_datetime']):
                trades_open[g] = t
            return trades_open
        return None

    def get_close_trades_by_date(self, trading_date):
        # 执行
        if trading_date in self._trades_close:
            trades_close = {}
            tmp = self._trades_close[trading_date]
            for g,t in tmp.groupby(['trading_datetime']):
                trades_close[g] = t
            return trades_close
        return None



__config__ = {
  "base": {
    "start_date": "2012-01-01",
    "end_date": "2021-12-31",
    "frequency": "1d",
    "forced_liquidation": False,
    "accounts": {
        "stock": 10000000
    }
  },
  "extra": {
    "log_level": "error",
  },
  "mod": {
    "sys_analyser": {
      "benchmark": "000300.XSHG",
      "enabled": True,
      "report_save_path": "report",
      "plot": False
    },
    "sys_simulation": {
      "enabled": True,
      "signal": True
    }
  }
}


if __name__ == '__main__':
    import time

    from rqalpha.mod.rqalpha_mod_sys_analyser.plot.plot import plot_result
    start = time.time()

    # import cProfile
    # pr = cProfile.Profile()
    # pr.enable()

    gt = GroupStockTrades('54e64a88-8f59-4d4a-8888-a001142e35db')
    ret = gt.concat_result_by_group()
    print(time.time() - start)

    # pp = PortfolioPlot('379f92ab-2e05-45b9-be5e-5b5e73a0c457')
    # ret = pp.get_results()

    # pr.disable()
    # pr.print_stats('tottime')
    
    plot_result(ret)
